<?php
	class Database_Tree extends Database {

		private static $instance_tree;

		/**
		 * @param string $config_name
		 *
		 * @return null|Database_Tree
		 */
		public static function getInstance($config_name = 'main') {
			if (empty(self::$instance_tree[$config_name])) {
				self::$instance_tree[$config_name] = new Database_Tree($config_name);
			}

			return self::$instance_tree[$config_name];
		}

		public function rebuildNS($table, $parent_field = 'parent_id', $key_field = 'id') {

			$this->query("DROP FUNCTION IF EXISTS rebuild_nested_set_tree");
			$this->query("
				-- Создаём ту самую функцию

				CREATE FUNCTION rebuild_nested_set_tree()
				RETURNS INT DETERMINISTIC MODIFIES SQL DATA
				BEGIN
				-- Изначально сбрасываем все границы в NULL
					UPDATE {$table} t SET lft = NULL, rgt = NULL, lvl = 0;

					-- Устанавливаем границы корневым элементам
					SET @i := 0;
					UPDATE {$table} t SET lft = (@i := @i + 1), rgt = (@i := @i + 1) WHERE t.{$parent_field} = 0;

					forever: LOOP

						SET @parent_id := 0;
						SELECT t.{$key_field}, t.rgt FROM {$table} t, {$table} tc
						WHERE t.{$key_field} = tc.parent_id AND tc.lft IS NULL AND t.rgt IS NOT NULL
						ORDER BY t.rgt LIMIT 1 INTO @parent_id, @parent_right;

						IF @parent_id = 0 THEN LEAVE forever; END IF;

						SET @current_left := @parent_right;

						SELECT @current_left + COUNT(*) * 2 FROM {$table} WHERE {$parent_field} = @parent_id INTO @parent_right;

						SET @current_length := @parent_right - @current_left;

						UPDATE {$table} t SET rgt = rgt + @current_length WHERE rgt >= @current_left ORDER BY rgt;
						UPDATE {$table} t SET lft = lft + @current_length WHERE lft > @current_left ORDER BY lft;

						-- И только сейчас обновляем границы текущего ряда
						SET @i := (@current_left - 1);

						UPDATE {$table} t SET lft = (@i := @i + 1), rgt = (@i := @i + 1) WHERE {$parent_field} = @parent_id ORDER BY {$key_field};
					END LOOP;

					-- Возвращаем самый самую правую границу для дальнейшего использования
					RETURN (SELECT MAX(rgt) FROM {$table} t);
				END");

			$this->query("SELECT rebuild_nested_set_tree()");

			$q = $this->query("SELECT node.{$key_field} as `id`, (COUNT(parent.{$key_field}) - 1) AS `lvl`
				FROM {$table} AS node,
				{$table} AS parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				GROUP BY node.{$key_field}
				ORDER BY node.lft"
			);

			if ($this->numRows($q)) {
				$sql = "INSERT INTO `{$table}`(`{$key_field}`,`lvl`) VALUES";
				while ($row = $this->fetch($q)) {
					$sql .= " ({$row['id']}, {$row['lvl']}),";
				}
				$sql = mb_substr($sql, 0, -1) . ' ON DUPLICATE KEY UPDATE `lvl`=VALUES(`lvl`)';
				$this->query($sql);
			}


//			$this->query("DROP FUNCTION IF EXISTS rebuild_nested_set_tree");

		}
	}